package lxt.dao.impl;

import java.io.Serializable;
import java.util.List;

import org.hibernate.SQLQuery;

import lxt.dao.InvoiceInfoDao;
import lxt.entity.InvoiceInfo;

public class InvoiceInfoDaoImpl extends ABseDaoImpl implements InvoiceInfoDao {
	/**
	 * 获得插入的值的Id
	 * @param object
	 * @return
	 */
	public Serializable saveInvo(Object object)
	{
		return  getHibernateTemplate().save(object);
	}
	/**
	 * 关联查询运输方案
	 */
	@SuppressWarnings("unchecked")
	public List<Object> queryInvoiceInfoById(String orderId) {
		StringBuffer sql = new StringBuffer();
		//自己车sql
		sql.append("select ti.id,ti.order_id,ti.truck_id,group_concat(tdi.name)name,ti.load_pertime,tio.unit_price,ti.trans_times,ti.trans_times*ti.load_pertime*tio.unit_price total" +
				   " from tb_invoice_info ti,tb_driver_workload td,tb_driver_info tdi,tb_invoice_order tio" +
				   " where ti.id = td.invoice_id and td.driver_id = tdi.driver_id and ti.order_id = tio.order_id  ");
		if(orderId!=null&&!orderId.equals("")){
			sql.append(" and ti.order_id ='"+orderId+"'");
		}
		sql.append(" group by ti.id,ti.trans_times,ti.load_pertime");
		sql.append(" UNION");
		//租车
		sql.append(" select ti.id,ti.order_id,ti.truck_id,'-' name,ti.load_pertime,tio.unit_price,ti.trans_times,ti.trans_times * ti.load_pertime total" +
				   " from tb_invoice_info ti, tb_borrow_truck_info td,tb_invoice_order tio" +
				   " WHERE ti.id = td.invoice_id and ti.order_id = tio.order_id ");
		if(orderId!=null&&!orderId.equals("")){
			sql.append(" AND ti.order_id ='"+orderId+"'");
		}
		sql.append(" group by ti.id,ti.trans_times,ti.load_pertime");
		
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		
		return query.list();
	}
	/**
	 * 派单查询
	 */
	@SuppressWarnings("unchecked")
	public List<Object> queryDistribute(String orderId,String truckId){
		StringBuffer sql = new StringBuffer();
		//自己车sql
		sql.append("select ti.id,ti.order_id,ti.truck_id,tdi.name,ti.load_pertime,tio.unit_price,ti.trans_times,ti.trans_times*ti.load_pertime total" +
				   " from tb_invoice_info ti,tb_driver_workload td,tb_driver_info tdi,tb_invoice_order tio" +
				   " where ti.id = td.invoice_id and td.driver_id = tdi.driver_id and ti.order_id = tio.order_id  ");
		if(orderId!=null&&!orderId.equals("")){
			sql.append(" and ti.order_id ='"+orderId+"'");
		}
		if(truckId!=null&&!truckId.equals("")){
			sql.append(" and ti.truck_id like '%"+truckId+"%'");
		}
		sql.append(" UNION");
		//租车
		sql.append(" select ti.id,ti.order_id,ti.truck_id,'-' name,ti.load_pertime,tio.unit_price,ti.trans_times,ti.trans_times * ti.load_pertime total" +
				   " from tb_invoice_info ti, tb_borrow_truck_info td,tb_invoice_order tio" +
				   " WHERE ti.id = td.invoice_id and ti.order_id = tio.order_id ");
		if(orderId!=null&&!orderId.equals("")){
			sql.append(" AND ti.order_id ='"+orderId+"'");
		}
		if(truckId!=null&&!truckId.equals("")){
			sql.append(" and ti.truck_id like '%"+truckId+"%'");
		}
		SQLQuery query = this.getSession().createSQLQuery(sql.toString());
		return query.list();
	}
	/**
	 * 根据订单ID查询invoiceInfo 
	 */
	@SuppressWarnings("unchecked")
	public List<InvoiceInfo> queryOrder(String orderId){
		StringBuffer hql = new StringBuffer(" Select a from InvoiceInfo a where a.invoiceOrder.orderId = ? ");
		return this.getHibernateTemplate().find(hql.toString(),orderId);	
	}
}
